package ru.hh.team02.chat.database;

import ru.hh.team02.chat.messaging.Message;
import ru.hh.team02.chat.messaging.PrivateMessage;
import ru.hh.team02.chat.server.Services;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DatabaseServiceDerby implements DatabaseServiceInterface {
    private static final String DB_URL = "jdbc:derby:hh0001team02;create=false";
    private static final String DB_USER = "team02";
    private static final String DB_PASS = "team02";
    private PreparedStatement clientExists;
    private PreparedStatement getPasswordByClientName;
    private PreparedStatement getRoomNameByClientName;
    private PreparedStatement putNewPersonalMessageIntoDatabase;
    private PreparedStatement markPersonalMessageAsDelivered;
    private PreparedStatement getAllUndeliveredMessagesToTheClient;
    private PreparedStatement getMessagesById;
    private PreparedStatement getAllRooms;
    private PreparedStatement changeRoom;

    public DatabaseServiceDerby() {
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            //Get a connection
            Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
            clientExists = conn.prepareStatement("select id from users " +
                    "where name = ?");
            getPasswordByClientName = conn.prepareStatement("select password from users " +
                    "where name = ?");
            getRoomNameByClientName = conn.prepareStatement("select name from rooms where id in " +
                    "(select room_id from users where name = ?)");
            putNewPersonalMessageIntoDatabase = conn.prepareStatement("insert into messages(message, rcptto_id) " +
                    "values (?, ?)", Statement.RETURN_GENERATED_KEYS);
            markPersonalMessageAsDelivered = conn.prepareStatement("delete from messages where id = ?");
            getAllUndeliveredMessagesToTheClient = conn.prepareStatement("select id, message from messages where rcptto_id in " +
                    "(select id from users " +
                    "where name = ?)");
            getMessagesById = conn.prepareStatement("select message from messages " +
                    "where id = ?");
            getAllRooms = conn.prepareStatement("select name from rooms");
            changeRoom = conn.prepareStatement("update users set room_id = " +
                    "(select id from rooms where rooms.name = ?) " +
                    "where name = ?");
        } catch (Exception except) {
            Services.ADMIN_LOGGER.write(except.getMessage());
        }
    }

    @Override
    public boolean clientExists(String name) {
        boolean result = false;
        try {
            clientExists.setString(1, name);
            ResultSet resultSet = clientExists.executeQuery();
            try {
                result = (resultSet.next());
            }
            finally {
                resultSet.close();
            }
        } catch (SQLException sqlExcept) {
            Services.ADMIN_LOGGER.write(sqlExcept.getMessage());
        }
        return result;
    }

    @Override
    public String getPasswordByClientName(String name) {
        String result = null;
        try {
            getPasswordByClientName.setString(1, name);
            ResultSet resultSet = getPasswordByClientName.executeQuery();
            try {
                if (resultSet.next()) {
                    result = resultSet.getString("password");
                }
            }
            finally {
                resultSet.close();
            }
        } catch (SQLException sqlExcept) {
            Services.ADMIN_LOGGER.write(sqlExcept.getMessage());
        }
        return result;
    }

    @Override
    public String getRoomNameByClientName(String name) {
        String result = null;
        try {
            getRoomNameByClientName.setString(1, name);
            ResultSet resultSet = getRoomNameByClientName.executeQuery();
            try {
                if (resultSet.next()) {
                    result = resultSet.getString("name");
                }
            }
            finally {
                resultSet.close();
            }
        } catch (SQLException sqlExcept) {
            Services.ADMIN_LOGGER.write(sqlExcept.getMessage());
        }
        return result;
    }

    @Override
    public int putNewPersonalMessageIntoDatabase(String message, String recepientName) {
        int result = 0;
        try {
            int clientId;
            clientExists.setString(1, recepientName);
            ResultSet resultSet = clientExists.executeQuery();
            try {
                if (resultSet.next()) {
                    clientId = resultSet.getInt("id");
                    putNewPersonalMessageIntoDatabase.setString(1, message);
                    putNewPersonalMessageIntoDatabase.setInt(2, clientId);
                    putNewPersonalMessageIntoDatabase.executeUpdate();
                    ResultSet generatedKeys = putNewPersonalMessageIntoDatabase.getGeneratedKeys();
                    try {
                        if (generatedKeys.next()) {
                            result = generatedKeys.getInt(1);
                        }
                    }
                    finally {
                        generatedKeys.close();
                    }
                }
            }
            finally {
                resultSet.close();
            }
        } catch (SQLException sqlExcept) {
            Services.ADMIN_LOGGER.write(sqlExcept.getMessage());
        }

        Services.ADMIN_LOGGER.write(message + " has been stored into database");
        return result;
    }

    @Override
    public boolean markPersonalMessageAsDelivered(int id) {
        String message = getMessagesById(id).getText();

        try {
            markPersonalMessageAsDelivered.setInt(1, id);
            markPersonalMessageAsDelivered.execute();
        } catch (SQLException sqlExcept) {
            Services.ADMIN_LOGGER.write("trying to delete unknown message");
            return false;
        }

        Services.ADMIN_LOGGER.write(message + " has been delivered and deleted from DB.");
        return true;
    }

    @Override
    public List<Message> getAllUndeliveredMessagesToTheClient(String clientName) {
        List<Message> result = new ArrayList<>();
        try {
            getAllUndeliveredMessagesToTheClient.setString(1, clientName);
            ResultSet resultSet = getAllUndeliveredMessagesToTheClient.executeQuery();
            try {
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String message  = resultSet.getString("message");
                    result.add(new PrivateMessage(message, id));
                }
            }
            finally {
                resultSet.close();
            }
        } catch (SQLException sqlExcept) {
            Services.ADMIN_LOGGER.write(sqlExcept.getMessage());
            return null;
        }

        for (Message m : result) {
            String message = m.getText();
            Services.ADMIN_LOGGER.write(message + " is on the way");
        }

        return result;
    }

    @Override
    public PrivateMessage getMessagesById(int id) {
        PrivateMessage result = null;
        try {
            getMessagesById.setInt(1, id);
            ResultSet resultSet = getMessagesById.executeQuery();
            try {
                if (resultSet.next()) {
                    String message  = resultSet.getString("message");
                    result = new PrivateMessage(message, id);
                }
            }
            finally {
                resultSet.close();
            }
        } catch (SQLException sqlExcept) {
            Services.ADMIN_LOGGER.write(sqlExcept.getMessage());
        }
        return result;
    }

    @Override
    public String[] getAllRooms() {
        List<String> result = new ArrayList<String>();
        try {
            ResultSet resultSet = getAllRooms.executeQuery();
            try {
                while (resultSet.next()) {
                    result.add(resultSet.getString("name"));
                }
            }
            finally {
                resultSet.close();
            }
        } catch (SQLException sqlExcept) {
            Services.ADMIN_LOGGER.write(sqlExcept.getMessage());
            return null;
        }


        return result.toArray(new String[result.size()]);
    }

    @Override
    public void changeRoom(String clientName, String room) {
        try {
            changeRoom.setString(1, clientName);
            changeRoom.setString(2, room);
            changeRoom.executeUpdate();
        } catch (SQLException sqlExcept) {
            Services.ADMIN_LOGGER.write(sqlExcept.getMessage());
        }
    }
}
